package com.medical.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.medical.entity.CourseSeeing;

public interface CourseSeeingMapper extends BaseMapper<CourseSeeing> {
    int deleteByPrimaryKey(Integer id);
    
    int insertSelective(CourseSeeing record);

    CourseSeeing selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(CourseSeeing record);

    int updateByPrimaryKey(CourseSeeing record);
    
    @Select("SELECT ifnull(AVG(ce.score),0.0) FROM t_course_evaluate as ce LEFT JOIN t_course_seeing as cs ON ce.courseid=cs.id WHERE  ce.coursetype=1 AND cs.schoolid=#{schoolId} UNION ALL SELECT ifnull(AVG(ce.score),0.0) FROM t_course_evaluate as ce LEFT JOIN t_course_streaming as cs ON ce.courseid=cs.id WHERE  ce.coursetype=2 AND cs.schoolid=#{schoolId} UNION ALL SELECT ifnull(AVG(ce.score),0.0) FROM t_course_evaluate as ce LEFT JOIN t_course_offline as cs ON ce.courseid=cs.id WHERE  ce.coursetype=3 AND cs.schoolid=#{schoolId}")
    public List<Double> selectAvgScore(@Param("schoolId")int schoolId);
    
    @Select("SELECT id,images,`name`,time,1 as type FROM t_course_seeing WHERE userid=#{userId} UNION SELECT id,images,`name`,time,2 as type FROM t_course_offline WHERE userid=#{userId} UNION SELECT id,images,`name`,time,3 as type FROM t_course_streaming WHERE userid=#{userId}")
    public List<Map<String, Object>> selectByTeacherId(@Param("userId")int userId);
    
    @Select("(SELECT cs.id,cs.images,cs.`name`,cs.`hour`,1 as type,cs.totalprice,u.nickname as teacherName  FROM t_course_seeing as cs LEFT JOIN t_user as u ON cs.userid=u.id WHERE schoolid=#{schoolId}) UNION (SELECT co.id,co.images,co.`name`,co.`hour`,3 as type,co.totalprice,u.nickname as teacherName FROM t_course_offline as co LEFT JOIN t_user as u ON co.userid=u.id WHERE schoolid=#{schoolId} )  UNION (SELECT cs.id,cs.images,cs.`name`,0,2 as type,cs.totalprice,u.nickname as teacherName FROM t_course_streaming as cs LEFT JOIN t_user as u ON cs.userid=u.id WHERE schoolid=#{schoolId} ) limit #{pageIndex},#{size}")
    public List<Map<String, Object>> selectBySchoolId(@Param("schoolId")int schoolId,@Param("pageIndex")int pageIndex,@Param("size")int size);
    
    @Select("(SELECT cs.id,cs.images,cs.`name`,cs.`hour`,1 as type,cs.totalprice,u.nickname as teacherName  FROM t_course_seeing as cs LEFT JOIN t_user as u ON cs.userid=u.id WHERE schoolid=#{schoolId}) UNION (SELECT co.id,co.images,co.`name`,co.`hour`,3 as type,co.totalprice,u.nickname as teacherName FROM t_course_offline as co LEFT JOIN t_user as u ON co.userid=u.id WHERE schoolid=#{schoolId} )  UNION (SELECT cs.id,cs.images,cs.`name`,0,2 as type,cs.totalprice,u.nickname as teacherName FROM t_course_streaming as cs LEFT JOIN t_user as u ON cs.userid=u.id WHERE schoolid=#{schoolId} )")
    public List<Map<String, Object>> selectBySchoolIdCount(@Param("schoolId")int schoolId);
    
    @Select("SELECT * FROM t_course_seeing WHERE 1=1 ${sql} ${order} limit #{pageIndex},#{size}")
    public List<Map<String, Object>> getList(@Param("sql")String sql,@Param("order") String order,@Param("pageIndex")int pageIndex,@Param("size")int size);
    
    @Select("SELECT count(1) FROM t_course_seeing WHERE 1=1 ${sql}")
    public Integer getListCount(@Param("sql")String sql);
    
    @Select("SELECT * FROM t_course_seeing as cs WHERE cs.id and buystates=false and name like #{keyword} and isspelling=true limit #{pageIndex},#{size}")
    public List<Map<String, Object>> selectInIds(@Param("pageIndex")int pageIndex,@Param("size")int size,@Param("keyword")String keyword);
    
    @Select("SELECT count(1) FROM t_course_seeing as cs WHERE cs.id and buystates=false and isspelling=true")
    public Integer selectInIdsCount();
    
    @Update("UPDATE t_course_seeing SET `release`=#{release} WHERE id=#{id}")
    public Integer updateRelease(@Param("release")Boolean release,@Param("id")int id);
    
    @Select("SELECT id,images,`name`,totalprice,`release`,5 as type FROM t_course_seeing WHERE userid=#{userId} UNION ALL SELECT id,images,`name`,totalprice,`release`,3 as type FROM t_course_streaming WHERE userid=#{userId} UNION ALL SELECT id,images,`name`,totalprice,`release`,4 as type FROM t_course_offline WHERE userid=#{userId}")
    public List<Map<String, Object>> getByUserId(@Param("userId")int userId);
}